﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using HZ.MBSM.DBUtility;

public partial class Supervise_ImgeShow : System.Web.UI.Page, HZ.MBSM.BLL.IPageDescribe
{
    bool bPrint = false;
    int nSelectArear = 0;
    private string strArea = "", Area = "";
    //要显示的字段
    private string strFile = "AreaPrice.goodsid as 流水号,SalerProduct.ProductName as 通用名,SalerProduct.Medicinemodel as 剂型,dbo.SalerProduct.Outlookc as 规格,SalerProduct.Unit as 单位,SalerProduct.factor as 转换系数,SalerProduct.cengci as 质量层次,SalerProduct.CompanyName_SC as 生产企业";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (HZ.MBSM.BLL.Login.CheckAssert(this.UseAccountType))
        {

            if (!Page.IsPostBack)
            {
                bandinCheck();
            }

            GridViewShow();
        }
    }
    private void bandinCheck()
    {
        string strSqlCheck = "";
        Db db = new Db();
        if (rbl1.SelectedValue == "5")
        {
           strSqlCheck= "select * from zonePriceDzb ";
        }
        else
        {
            strSqlCheck = "select * from zonePriceDzb where zoneType=" + rbl1.SelectedValue;
        }
        DataTable da = new DataTable();
        da = db.DataTable(strSqlCheck);
        CkcL.DataSource = da;
        CkcL.DataTextField = "zonename";
        CkcL.DataValueField = "zonecode";
        CkcL.DataBind();
        db.Close();

    }
    private void GridViewShow()
    {
        Db db = new Db();
        DbPage dbPage = new DbPage(db);
        dbPage.Table = "dbo.AreaPrice INNER JOIN dbo.SalerProduct ON dbo.AreaPrice.goodsid = dbo.SalerProduct.goodsid";                                               //要查询的表明
        dbPage.Key = "AreaPrice.goodsid";                                               //表的主键    
        dbPage.Where = Conditions();                                           //查询条件    
        dbPage.SelectField = SelectField();//要显示的字段
        dbPage.PageIndex = SysFun.ToInt(Request["CurrentlyPageIndex"]);          //要显示第几页   
        if (bPrint)
        {
            dbPage.PageSize = 1000000;
        }
        else
        {
            dbPage.PageSize = 10;                                                    //没有显示记录条数   
        }
        dbPage.OrderBy = "ProductName,medicinemodel,outlookc,AreaPrice.goodsid";                  //排序字段
        System.Data.IDataReader dr = dbPage.ExecuteReader();
        GridView1.DataSource = dr;
        GridView1.DataBind();
        dr.Close();
        dr.Dispose();
        dbPage.Db.Close();
        db.Close();
        this.DbPageFlip1.DbPage = dbPage;//将数据传给控键
    }


    private string SelectField()//要显示的字段
    {
        return strFile + strArea;
    }
    protected void Button5_Click1(object sender, EventArgs e)
    {
    }
    private string Conditions()//查询条件
    {
        strArea = ""; nSelectArear = 0;
        Area="";
        bool bselet =false; //是否选择了地区
        for (int i = 0; i < CkcL.Items.Count; i++)
        {
            if (CkcL.Items[i].Selected)
            {
                strArea += ",convert(numeric(20,2)," + CkcL.Items[i].Value + ") as " + CkcL.Items[i].Text;
                Area += "," + CkcL.Items[i].Value + '|' + CkcL.Items[i].Text;
                bselet = true;
                nSelectArear++;  //选择了几个地区
            }
        }
        if (rbl1.SelectedValue == "1") //药店
        {
            strArea += ",convert(numeric(20,2),DrugstoresPrice) as 药店加权平均价,Stores as 药店";
        }
        if (rbl1.SelectedValue == "2") //18地市
        {
            strArea += ", convert(numeric(20,2),HNAreaPrice) as '18地市最低价',Area as 地市";
        }
        if (rbl1.SelectedValue == "3") //10省
        {
            strArea += ",convert(numeric(20,2),TenStatePrice ) as '10省最低价',State as 城市 ";
        }
        if (rbl1.SelectedValue == "4") //10医院
        {
            strArea += ",convert(numeric(20,2),HNTenHospitalPrice ) as '10医院最低价',TenHospitals as 医院 ";
        }
        if (rbl1.SelectedValue == "5") //全部
        {
            strArea += ",convert(numeric(20,2),DrugstoresPrice) as 药店加权平均价,convert(numeric(20,2),HNAreaPrice) as  '18地市最低价',convert(numeric(20,2),TenStatePrice ) as'10省最低价',convert(numeric(20,2),HNTenHospitalPrice ) as '10医院最低价',convert(numeric(20,2),BidLowPrice ) as 底标价,convert(numeric(20,2),GoUpSize) as 涨幅,convert(numeric(20,2),FinalBidLowPrice) as 最终标底";
        }
        if (bPrint) 
        { }
        else
        {
            if (bselet)
            {
                switch (rbl1.SelectedValue)
                {
                    case "1": //药店

                        strArea += ",AreaPrice.goodsid as 柱状图,AreaPrice.goodsid as 折线图"; //手动
                        //strArea += ",AreaPrice.goodsid as 图形"; //Owc11
                       // Area = Area.Length > 0 ? Area.Remove(0, 1) : "";
                        break;
                    case "2"://18地市

                        strArea += ",AreaPrice.goodsid as 柱状图,AreaPrice.goodsid as 折线图"; //手动
                        //strArea += ",AreaPrice.goodsid as 图形"; //Owc11
                       // Area = Area.Length > 0 ? Area.Remove(0, 1) : "";
                        break;
                    case "3"://10省

                        strArea += ",AreaPrice.goodsid as 柱状图,AreaPrice.goodsid as 折线图"; //手动
                        //strArea += ",AreaPrice.goodsid as 图形"; //Owc11
                       // Area = Area.Length > 0 ? Area.Remove(0, 1) : "";
                        break;
                    case "4"://10医院

                        strArea += ",AreaPrice.goodsid as 柱状图,AreaPrice.goodsid as 折线图"; //手动
                        //strArea += ",AreaPrice.goodsid as 图形"; //Owc11
                        // Area = Area.Length > 0 ? Area.Remove(0, 1) : "";
                        break;
                    case "5"://全部

                        break;
                }
            }
        }
        string strConditions = "", Goodsid = "", ProductCode = "", ProductName = "", Medicinemodel = "", Outlookc = "", CompanyName_SC = "", UserCode_SC = "";
        strConditions = " year=" + DropDownList1.SelectedValue;
        Goodsid = txtLSH.Text.Trim();
        if (Goodsid != "")
        {
            strConditions += " and AreaPrice.Goodsid=" + Goodsid;
        }
        ProductCode = SysFun.ToName(txtProductCode.Text.Trim());
        if (ProductCode != "")
        {
            strConditions += " and SalerProduct.ProductCode='" + ProductCode + "'";
        }
        ProductName = SysFun.ToName(txtname.Text.Trim());
        if (ProductName != "")
        {
            strConditions += " and SalerProduct.ProductName like '%" + ProductName + "%'";
        }
        Medicinemodel = SysFun.ToName(txtmedicinemodel.Text.Trim());
        if (Medicinemodel != "")
        {
            strConditions += " and SalerProduct.Medicinemodel like '%" + Medicinemodel + "%'";
        }
        Outlookc = SysFun.ToName(txtoutlook.Text.Trim());
        if (Outlookc != "")
        {
            strConditions += " and SalerProduct.Outlookc like '%" + Outlookc + "%'";
        }
        UserCode_SC = SysFun.ToName(txtUserCode.Text.Trim());
        if (UserCode_SC != "")
        {
            strConditions += " and SalerProduct.UserCode_SC like '%" + UserCode_SC + "%'";
        }
        CompanyName_SC = SysFun.ToName(txtCompanyName.Text.Trim());
        if (CompanyName_SC != "")
        {
            strConditions += " and SalerProduct.CompanyName_SC like '%" + CompanyName_SC + "%'";
        }
        return strConditions;
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        string strRowCell = "";
        if (bPrint) return;  //打印就不显示查看链接
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            
             for(int i=1;i<=nSelectArear;i++)
             {
                strRowCell = e.Row.Cells[7 + i].Text;
                e.Row.Cells[7 + i].Text = "<a  href=ReportShow.aspx?Goodsid=" + e.Row.Cells[0].Text + "&Area=" + Area.Split(',')[i].ToString() + "&Type=3 target='_blank'>" + strRowCell + "</a>";
            }
            if ((e.Row.Cells.Count > 12)&&rbl1.SelectedValue!="5")
            {
                strRowCell = Area.Length > 0 ? Area.Remove(0, 1) : "";
                //手动绘画
                e.Row.Cells[e.Row.Cells.Count - 2].Text = "<a  href=ReportShow.aspx?Goodsid=" + e.Row.Cells[0].Text + "&Year=" + DropDownList1.SelectedValue + "&Area=" + strRowCell + "&Type=1 target='_blank'>查看</a>";
                e.Row.Cells[e.Row.Cells.Count - 1].Text = "<a  href=ReportShow.aspx?Goodsid=" + e.Row.Cells[0].Text + "&Year=" + DropDownList1.SelectedValue + "&Area=" + strRowCell + "&Type=2 target='_blank'>查看</a>";
               //ow11 绘画
               //e.Row.Cells[e.Row.Cells.Count - 1].Text = "<a  href=RepotShowImge.aspx?Goodsid=" + e.Row.Cells[0].Text + "&Year=" + DropDownList1.SelectedValue + "&Area=" + Area + " target='_blank'>查看</a>";

            }
        }
    }

    protected void rbl1_SelectedIndexChanged1(object sender, EventArgs e)
    {
        bandinCheck();
        switch (rbl1.SelectedValue)
        {
            case "1": //药店
               
                buttonavery.Text = "计算药店加权平均价";
                break;
            case "2"://18地市
               
                buttonavery.Text = "计算18地市最低价";
                break;
            case "3"://10省
               
                buttonavery.Text = "计算10省最低价";
                break;
            case "4"://10医院

                buttonavery.Text = "计算10医院最低价";
                break;
            case "5"://全部
               
                buttonavery.Text = "计算最标底价";
                break;
        }
    }
    protected void buttonavery_Click(object sender, EventArgs e) //计算平均值
    {
        string strSql = "",strAreatselect="";
        for (int i = 0; i < CkcL.Items.Count; i++)
        {
            if (CkcL.Items[i].Selected)
            {
                switch (rbl1.SelectedValue)
                {
                    case "1": //  计算药店加权平均价
                        
                    case "2"://18地市  计算18地市最低价

                    case "3"://10省 计算10省最低价

                    case "4"://10省 计算10医院最低价

                        strSql += "@insert into AreaPriceCount select goodsid,year,'" + CkcL.Items[i].Value + "'," + CkcL.Items[i].Value + " from AreaPrice where [Year] =" + DropDownList1.SelectedValue;
                        strAreatselect += ","+CkcL.Items[i].Text;
                        break;
                    case "5"://全部 计算最标底价
                        strSql = "@";
                        break;
    
                }  
            }
        }
        if ((strSql.Length > 0) || (rbl1.SelectedValue=="5"))
        {
            if (rbl1.SelectedValue == "1")//计算药店加权平均价 (sun-max-min)/(count-2) when count >=3  sun/count when cunt<3;
            {
                strSql += "@delete from AreaPriceCount";
                strSql += "@delete from AreaPriceCount where ArearPrice is null or ArearPrice=0"; //去掉价格为空和为0的记录
                strSql += "@if exists (select 1  from  sysobjects  where  id = object_id('tempaa')  and   type = 'U')  drop table tempaa";
                strSql += "@select goodsid,Year,max(ArearPrice) as maxbidPrice,min(ArearPrice) as minbidPrice,sum(ArearPrice) as sumbidPrice,count(*) as cun,convert(float,0) as avprice into tempaa from AreaPriceCount group by goodsid,Year";
                strSql += "@update tempaa set avprice=(sumbidPrice-maxbidPrice-minbidPrice)/(cun-2) where cun >=3";
                strSql += "@update tempaa set avprice=sumbidprice/cun where cun<=2";
                strSql += "@update areaprice set DrugstoresPrice=avprice,Stores='" + strAreatselect.Remove(0, 1) + "' from tempaa where areaprice.goodsid=tempaa.goodsid and areaprice.year=tempaa.year";
                strSql += "@drop table tempaa";
                strSql += "@delete from AreaPriceCount";
            }
            else if (rbl1.SelectedValue == "2") //计算18地市最低价 min();
            {
                strSql += "@delete from AreaPriceCount";
                strSql += "@delete from AreaPriceCount where ArearPrice is null or ArearPrice=0"; //去掉价格为空和为0的记录
                strSql += "@if exists (select 1  from  sysobjects  where  id = object_id('tempaa')  and   type = 'U')  drop table tempaa";
                strSql += "@select goodsid,Year,min(ArearPrice) as minbidPrice,convert(float,0) as avprice into tempaa from AreaPriceCount group by goodsid,Year";
                strSql += "@update tempaa set avprice=minbidPrice ";
                strSql += "@update areaprice set HNAreaPrice=avprice,Area='" + strAreatselect.Remove(0, 1) + "' from tempaa where areaprice.goodsid=tempaa.goodsid and areaprice.year=tempaa.year";
                strSql += "@drop table tempaa";
                strSql += "@delete from AreaPriceCount";
            }
            else if (rbl1.SelectedValue == "3") //计算10省最低价 min();
            {
                strSql += "@delete from AreaPriceCount";
                strSql += "@delete from AreaPriceCount where ArearPrice is null or ArearPrice=0"; //去掉价格为空和为0的记录
                strSql += "@if exists (select 1  from  sysobjects  where  id = object_id('tempaa')  and   type = 'U')  drop table tempaa";
                strSql += "@select goodsid,Year,min(ArearPrice) as minbidPrice,convert(float,0) as avprice into tempaa from AreaPriceCount group by goodsid,Year";
                strSql += "@update tempaa set avprice=minbidPrice ";
                strSql += "@update areaprice set TenStatePrice=avprice,State='" + strAreatselect.Remove(0, 1) + "' from tempaa where areaprice.goodsid=tempaa.goodsid and areaprice.year=tempaa.year";
                strSql += "@drop table tempaa";
                strSql += "@delete from AreaPriceCount";
            }
            else if (rbl1.SelectedValue == "4") //计算10医院最低价 min();
            {
                strSql += "@delete from AreaPriceCount";
                strSql += "@delete from AreaPriceCount where ArearPrice is null or ArearPrice=0"; //去掉价格为空和为0的记录
                strSql += "@if exists (select 1  from  sysobjects  where  id = object_id('tempaa')  and   type = 'U')  drop table tempaa";
                strSql += "@select goodsid,Year,min(ArearPrice) as minbidPrice,convert(float,0) as avprice into tempaa from AreaPriceCount group by goodsid,Year";
                strSql += "@update tempaa set avprice=minbidPrice ";
                strSql += "@update areaprice set HNTenHospitalPrice=avprice,TenHospitals='" + strAreatselect.Remove(0, 1) + "' from tempaa where areaprice.goodsid=tempaa.goodsid and areaprice.year=tempaa.year";
                strSql += "@drop table tempaa";
                strSql += "@delete from AreaPriceCount";
            }
            else if (rbl1.SelectedValue == "5") //计算最标底价 min();
            {
                strSql += "@delete from AreaPriceCount";
                strSql  = "@insert into AreaPriceCount select goodsid,year,'DrugstoresPrice',DrugstoresPrice from dbo.AreaPrice where [Year]=" + DropDownList1.SelectedValue;
                strSql += "@insert into AreaPriceCount select goodsid,year,'HNAreaPrice',HNAreaPrice from dbo.AreaPrice where [Year]=" + DropDownList1.SelectedValue;
                strSql += "@insert into AreaPriceCount select goodsid,year,'HNTenHospitalPrice',HNTenHospitalPrice from dbo.AreaPrice where [Year]=" + DropDownList1.SelectedValue;
                strSql += "@insert into AreaPriceCount select goodsid,year,'TenStatePrice',TenStatePrice from dbo.AreaPrice where [Year]=" +DropDownList1.SelectedValue;
                strSql += "@delete from AreaPriceCount where ArearPrice is null or ArearPrice=0"; //去掉价格为空和为0的记录
                strSql += "@if exists(select 1 from sysobjects where id=object_id('tempaa') and type='U') drop table tempaa";
                strSql += "@select goodsid,year,min(arearprice) as minbidprice into tempaa from areapricecount group by goodsid,year";
                strSql += "@update areaprice set BidLowPrice=minbidprice from tempaa where areaprice.goodsid=tempaa.goodsid and areaprice.year=tempaa.year";
                strSql += "@update areaprice set FinalBidLowPrice=BidLowPrice*(1+GoUpSize)";
                strSql += "@drop table tempaa";
                strSql += "@delete from AreaPriceCount";
            }
            Db db = new Db();
            db.ExecuteTransaction(strSql.Substring(1).Split(new char[] { '@' }), 30);
            db.Close();
            GridViewShow();
        }
        else
        {
            Response.Write("<script>alert('请选择地区！');</script>");
        }
    }
    protected void Button1_Click(object sender, EventArgs e) //导出到Excul
    {
        bPrint = true; GridViewShow(); PrintCompanyPrice();
    }
    protected void PrintCompanyPrice() //导出到Excul
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
        Response.AddHeader("Content-Disposition", "attachment; filename=11.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentEncoding = System.Text.Encoding.UTF7;  //解决将gridview导出成excel时出现乱码  
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);
        System.Text.StringBuilder strHtml = new System.Text.StringBuilder();
        strHtml.Append(oStringWriter.ToString().Substring(0, 197));

        strHtml.Append(oStringWriter.ToString().Substring(198, oStringWriter.ToString().Length - 215));
        strHtml.Append(oStringWriter.ToString().Substring(oStringWriter.ToString().Length - 16, 16));
        // 把文件流发送到客户端 
        Response.Write(strHtml.ToString());
        // 停止页面的执行
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //OverRide　为了使导出成Excel可行！
    }

    #region IPageDescribe 成员

    public string Name
    {
        get { return "标底生成系统"; }
    }

    public string Describe
    {
        get { return ""; }
    }

    public string ParentName
    {
        get { return "标底生成系统"; }
    }

    public System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> UseAccountType
    {
        get
        {
            System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> _AccountType = new System.Collections.Generic.List<HZ.MBSM.DAL.AccountType>();
            _AccountType.Add(HZ.MBSM.DAL.AccountType.enumAdmin);
            return _AccountType;
        }
    }

    #endregion
}
  



